﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using HCQ2_IDAL;
using HCQ2_Model;
using HCQ2_Model.APPModel.ParamModel;
using System.Globalization;
using System.Data;
using HCQ2_Model.VR.Params;
using HCQ2_Model.DocModel.APIResultModel;
using HCQ2_Model.WeiXinApiModel.ResultApiModel;
using HCQ2_Model.DocModel.APIParam;
using HCQ2_Model.VLine.OutputDto;
using HCQ2_Common.SQL;
using HCQ2_Model.VLine.Params;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRExperienceRecordDAL: IT_VRExperienceRecordDAL
    {
        //******************************************pc*********************************************
        /// <summary>
        ///  获取体验记录列表
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<T_VRExperienceRecord> GetRecordList(ExperRecordModel param)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} record.*,info.file_id,info.file_name,pass.cp_name FROM(SELECT er_id,file_id,file_name,user_id,user_name,folder_name,in_date,out_date,em_time,x_place,y_place,z_place,score, video_url,folder_code,file_code,", param.rows);
            switch (param.orderType)
            {
                case 2: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY score DESC)AS DispOrder "); break;//得分降序
                case 3: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY score ASC)AS DispOrder "); break;//得分升序
                default: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY in_date DESC)AS DispOrder "); break;//体验时间降序
            }
            sb.AppendFormat(" FROM dbo.T_VRExperienceRecord WHERE 1=1 ");
            //登录名，用户名
            if (!string.IsNullOrEmpty(param.userName))
                sb.AppendFormat(" AND user_name like '%{0}%' ", param.userName);
            //进入时间，退出时间
            if (!string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date>= '{0}' AND in_date<='{1}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.inDate) && string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date>= '{0}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date<= '{0}' ", DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb.AppendFormat(" )record INNER JOIN (SELECT file_name, file_id FROM dbo.T_DocumentInfo WHERE 1 = 1 ");
            //体验作品
            if (!string.IsNullOrEmpty(param.fileName))
                sb.AppendFormat(" AND  file_name like '%{0}%' ", param.fileName);
            sb.AppendFormat(" )info ON record.file_id=info.file_id LEFT JOIN (SELECT cp_name, cp_code, file_id FROM dbo.T_VRCustomPass WHERE 1 = 1 ");
            //体验项目
            if (!string.IsNullOrEmpty(param.folderName))
                sb.AppendFormat(" AND cp_name like '%{0}%' ", param.folderName);
            sb.AppendFormat(" )pass ON pass.cp_code=record.folder_code AND pass.file_id=info.file_id WHERE record.DispOrder>{0};", (param.page - 1) * param.rows);
            #region 注释
            /************************************************************************/
            /*     
             *     sb.AppendFormat("SELECT TOP {0} * FROM(SELECT *,  ", param.rows);
            switch (param.orderType)
            {
                case 2: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY score DESC)AS DispOrder "); break;//得分降序
                case 3: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY score ASC)AS DispOrder "); break;//得分升序
                default: sb.AppendFormat(" ROW_NUMBER() OVER(ORDER BY in_date DESC)AS DispOrder "); break;//体验时间降序
            }
            sb.AppendFormat(" FROM T_VRExperienceRecord WHERE 1=1  ");
            //登录名，用户名
            if (!string.IsNullOrEmpty(param.userName))
                sb.AppendFormat(" AND user_name like '%{0}%' ", param.userName);
            //体验作品
            if (!string.IsNullOrEmpty(param.fileName))
                sb.AppendFormat(" AND  file_name like '%{0}%' ", param.fileName);
            //体验项目
            if (!string.IsNullOrEmpty(param.folderName))
                sb.AppendFormat(" AND folder_name like '%{0}%' ", param.folderName);
            //进入时间，退出时间
            if (!string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date>= '{0}' AND in_date<='{1}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.inDate) && string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date>= '{0}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date<= '{0}' ", DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb.AppendFormat(")record WHERE record.DispOrder>{0};", (param.page - 1) * param.rows);                                                     
            /************************************************************************/
            #endregion
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_VRExperienceRecord>(dt);
        }
        /// <summary>
        ///  获取体验列表记录数量
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetRecordNumCount(ExperRecordModel param)
        {
            sb?.Clear();
            sb.AppendFormat("SELECT COUNT(0) as num FROM T_VRExperienceRecord WHERE 1=1 ");
            //是否有无审核权限，有审核权限查看全部
            if (param.userId > 0)
                sb.AppendFormat(" AND user_id={0} ", param.userId);
            //登录名，用户名
            if (!string.IsNullOrEmpty(param.userName))
                sb.AppendFormat(" AND user_name like '%{0}%' ", param.userName);
            //体验作品
            if (!string.IsNullOrEmpty(param.fileName))
                sb.AppendFormat(" AND file_name like '%{0}%' ", param.fileName);
            //体验项目
            if (!string.IsNullOrEmpty(param.folderName))
                sb.AppendFormat(" AND folder_name like '%{0}%' ", param.folderName);
            //进入时间，退出时间
            if (!string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date>= '{0}' AND in_date<='{1}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(param.inDate) && string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date>= '{0}' ", DateTime.ParseExact(param.inDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (string.IsNullOrEmpty(param.inDate) && !string.IsNullOrEmpty(param.outDate))
                sb.AppendFormat(" AND in_date<= '{0}' ", DateTime.ParseExact(param.outDate, "yyyy-MM-dd", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            return HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(sb.ToString()));
        }
        /// <summary>
        ///  获取体验排行列表
        /// </summary>
        /// <param name="param">查询参数</param>
        /// <param name="total">返回记录数量</param>
        /// <returns></returns>
        public List<ExperienceRankModel> GetRecordRankList(ExperRecordModel param,string user_guid, out int total)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC,CAST(ISNULL(em_time,0) as decimal) ASC,in_date ASC)AS number,er_id,user_id,user_name,
	        file_id,file_name,folder_name,em_time,score,CONVERT(varchar(100),in_date,120) AS in_date,CONVERT(varchar(100),out_date,120) AS 
	        out_date FROM dbo.T_VRExperienceRecord WHERE 1=1 ", param.rows);
            StringBuilder sql = new StringBuilder();
            //课件名称
            if (!string.IsNullOrEmpty(param.fileName))
                sql.AppendFormat(@" AND file_name like '{0}%' ",param.fileName);
            //项目名称
            if (!string.IsNullOrEmpty(param.folderName))
                sql.AppendFormat(" AND folder_name like '{0}%' ", param.folderName);
            //用户
            if (!string.IsNullOrEmpty(param.userName))
                sql.AppendFormat(" AND user_id IN(SELECT user_id FROM dbo.T_User WHERE user_name LIKE '{0}%') ", param.userName);
            if (!string.IsNullOrEmpty(param.lineStatus))
            {
                //全校
                sql.AppendFormat(@" AND user_id in(select users.user_id from
	            (select classId,studentId from T_Student)st inner join
	            (select id from T_Class where schoolCode in(select schoolCode from T_Class where id IN(select classId from T_Student where ");
                if (string.IsNullOrEmpty(param.userName))
                    sql.AppendFormat(@" studentId IN(select studentId from t_user where user_guid='{0}'))))ct on st.classId=ct.id inner join
	            (select user_id,studentId from t_user)users on st.studentId=users.studentId) ", user_guid);
                else
                    sql.AppendFormat(@" studentId IN(select studentId from t_user where user_guid IN(SELECT user_guid FROM dbo.T_User WHERE USER_NAME LIKE '{0}%')))))ct on st.classId=ct.id inner join
	            (select user_id,studentId from t_user)users on st.studentId=users.studentId) ", param.userName);
            }
            //数量
            StringBuilder count = new StringBuilder();
            count.AppendFormat("SELECT COUNT(0) FROM dbo.T_VRExperienceRecord WHERE 1=1 ");
            if (!string.IsNullOrEmpty(sql.ToString()))
                count.AppendFormat(sql.ToString());
            total = HCQ2_Common.Helper.ToInt(HCQ2_Common.SQL.SqlHelper.ExecuteScalar(count.ToString()));
            sb.AppendFormat(sql.ToString());
            sb.AppendFormat(" )record WHERE record.number>{0};", (param.page - 1) * param.rows);
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ExperienceRankModel>(dt);
        }
        //******************************************接口*******************************************
        /// <summary>
        ///  获取体验记录列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<T_VRExperienceRecord> GetRecordListByUser(QueryRecordModel model, int userId)
        {
            sb?.Clear();
            sb.AppendFormat("SELECT * FROM T_VRExperienceRecord WHERE user_id={0} ",userId);
            if (model.file_id > 0)
                sb.AppendFormat(" AND file_id={0} ", model.file_id);
            if (!string.IsNullOrEmpty(model.folder_name))
                sb.AppendFormat(" AND folder_name='{0}' ", model.folder_name);
            if (!string.IsNullOrEmpty(model.in_date) && !string.IsNullOrEmpty(model.out_date))
                sb.AppendFormat(" AND in_date>= '{0}' AND out_date<='{1}' ", DateTime.ParseExact(model.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString(), DateTime.ParseExact(model.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if (!string.IsNullOrEmpty(model.in_date) && string.IsNullOrEmpty(model.out_date))
                sb.AppendFormat(" AND in_date>= '{0}' ", DateTime.ParseExact(model.in_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            else if(string.IsNullOrEmpty(model.in_date) && !string.IsNullOrEmpty(model.out_date))
                sb.AppendFormat(" AND out_date<= '{0}' ", DateTime.ParseExact(model.out_date, "yyyyMMddHHmmss", CultureInfo.CurrentCulture, DateTimeStyles.None).ToString());
            sb.AppendFormat(" ORDER BY user_id,file_id;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<T_VRExperienceRecord>(dt);
        }
        /// <summary>
        ///  获取体验排行：根据体验记录统计查询获得
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<ExperienceRankModel> GetPopularityRankList(QueryRecordModel model)
        {
            sb?.Clear();
            sb.AppendFormat(@"SELECT TOP {0} er_id,user_id,user_name,
	        file_id,file_name,folder_name,em_time,score,CONVERT(varchar(100),in_date,120) AS in_date,CONVERT(varchar(100),out_date,120) AS 
	        out_date,user_header=(SELECT user_img FROM dbo.T_User WHERE user_id=record.user_id) FROM dbo.T_VRExperienceRecord record WHERE 1=1 ", model.size);
            if (!string.IsNullOrEmpty(model.file_code))
                sb.AppendFormat(@" AND file_code='{0}' ", model.file_code);
            if (!string.IsNullOrEmpty(model.folder_code))
                sb.AppendFormat(" AND folder_code='{0}' ", model.folder_code);
            if (model.record_type == 1)
                //全校【用户所在学校】
                sb.AppendFormat(@" AND user_id in(select users.user_id from
	            (select classId,studentId from T_Student)st inner join
	            (select id from T_Class where schoolCode in(select schoolCode from T_Class where id=(select classId from T_Student where 
	            studentId=(select studentId from t_user where user_guid='{0}'))))ct on st.classId=ct.id inner join
	            (select user_id,studentId from t_user)users on st.studentId=users.studentId)", model.userid);
            sb.AppendFormat(" ORDER BY score DESC,CAST(em_time as decimal) ASC,in_date ASC;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<ExperienceRankModel>(dt);
        }
        /// <summary>
        ///  获取个人成绩
        /// </summary>
        /// <param name="mdel"></param>
        /// <returns></returns>
        public List<UserScoreRankModel> GetScoreByUser(HCQ2_Model.DocModel.APIParam.UserScoreParam model, int user_id)
        {
            sb?.Clear();
            sb.AppendFormat($@"SELECT top 1 record1.user_name,record1.em_time,record1.score,ISNULL(record2.rankGlobal,0)AS rank_global,ISNULL(reord3.rankSchool,0)AS rank_school FROM 
                (SELECT user_name,er_id,em_time,score FROM dbo.T_VRExperienceRecord WHERE file_code='{model.file_code}' AND folder_code='{model.folder_code}' AND user_id={user_id})record1 LEFT JOIN 
                (SELECT * from
	                (SELECT ROW_NUMBER() OVER(ORDER BY score DESC,CAST(em_time AS DECIMAL) ASC,in_date ASC) AS rankGlobal,er_id,user_id FROM dbo.T_VRExperienceRecord 
	                WHERE file_code='{model.file_code}' AND folder_code='{model.folder_code}')reord WHERE reord.user_id={user_id}
                )record2 ON record2.er_id = record1.er_id LEFT JOIN
                (
	                SELECT reord.er_id,reord.rankSchool from
	                (SELECT ROW_NUMBER() OVER(ORDER BY score DESC,CAST(em_time AS DECIMAL) ASC,in_date ASC) AS rankSchool,* FROM dbo.T_VRExperienceRecord 
	                WHERE file_code='{model.file_code}' AND folder_code='{model.folder_code}' AND user_id IN(
		                select users.user_id from
		                (select classId,studentId from T_Student)st inner join
		                (select id from T_Class where schoolCode in(select schoolCode from T_Class where id=(select classId from T_Student where 
		                studentId=(select studentId from t_user where user_guid='{model.userid}'))))ct on st.classId=ct.id inner join
		                (select user_id,studentId from t_user)users on st.studentId=users.studentId
	                ))reord WHERE reord.user_id={user_id} 
                )reord3 ON reord3.er_id = record1.er_id
                ORDER BY record2.rankGlobal,reord3.rankSchool;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<UserScoreRankModel>(dt);
        }
        /// <summary>
        ///  头记获取：比赛排名列表前10
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public List<GameRankModel> GetGameRankList(GameRank model, string in_date, string out_date)
        {
            sb?.Clear();
            sb.Append($@"SELECT TOP 10 * FROM(
            SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY score DESC, CAST(ISNULL(em_time, 0) AS DECIMAL) ASC, out_date ASC) AS number,
            user_id, user_name, file_id, file_name, folder_name, em_time, score, out_date FROM dbo.T_VRExperienceRecord
            WHERE file_code='{model.file_code}' AND folder_code='{model.cp_code}' AND in_date>='{in_date}' AND out_date<='{out_date}')record WHERE record.number = 1 ORDER BY record.score DESC, CAST(ISNULL(record.em_time, 0) AS DECIMAL) ASC,record.out_date ASC;");
            //sb.Append($"SELECT TOP 10 user_id,user_name,file_id,file_name,folder_name,em_time,score FROM dbo.T_VRExperienceRecord WHERE file_code='{model.file_code}' AND folder_code='{model.cp_code}' AND in_date>='{in_date}' AND out_date<='{out_date}' ORDER BY score DESC,CAST(ISNULL(em_time,0) AS DECIMAL) ASC,out_date ASC;");
            DataTable dt = HCQ2_Common.SQL.SqlHelper.ExecuteDataTable(sb.ToString());
            return HCQ2_Common.Data.DataTableHelper.DataTableToIList<GameRankModel>(dt);
        }
        /// <summary>
        ///  获取我的比赛排名站位
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public async Task<int> GetMyGameRank(MyGameRank model, int user_id, string in_date, string out_date)
        {
            sb?.Clear();
            sb.Append($@"SELECT ISNULL(result.numbers,0) AS number FROM (    
            SELECT  *, ROW_NUMBER() OVER(ORDER BY record.score DESC, CAST(ISNULL(record.em_time, 0) AS DECIMAL) ASC, record.out_date ASC)AS numbers FROM(
            SELECT ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY score DESC, CAST(ISNULL(em_time, 0) AS DECIMAL) ASC, out_date ASC) AS number,
            user_id, user_name, file_id, file_name, folder_name, em_time, score, out_date FROM dbo.T_VRExperienceRecord
            WHERE file_code = '{model.file_code}' AND folder_code = '{model.cp_code}' AND in_date >= '{in_date}' AND out_date <= '{out_date}')record
            WHERE record.number = 1)result WHERE result.user_id = {user_id};");
             return await Task.Run(() => { return HCQ2_Common.Helper.ToInt(SqlHelper.ExecuteScalar(sb.ToString())); });
        }
    }
}